package com.zhiyou.app.shop.website.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Component;

import com.zhiyou.app.shop.manager.dao.impl.BaseHibernateDAO;
import com.zhiyou.app.shop.website.dao.ILoclistDao;
import com.zhiyou.app.shop.website.dao.pojo.Loclist;
import com.zhiyou.app.shop.website.dao.pojo.LoclistId;

@Component
public class LoclistDaoImpl extends BaseHibernateDAO<Loclist, LoclistId> implements ILoclistDao {
	
	public LoclistDaoImpl(){
		super(Loclist.class);
	}
	
	@SuppressWarnings("unchecked")
	public List<Loclist> findAllCountry(){
		final String sql = " select distinct * from loclist group by location ";
		List<Loclist> list = hibernateTemplate.execute(new HibernateCallback<List<Loclist>>() {
			public List<Loclist> doInHibernate(Session session)
					throws HibernateException {
				Query query = session.createSQLQuery(sql);
				List list = query.list();
				List<Loclist> loclist = new ArrayList<Loclist>();
				//List Loclists = getSession().createSQLQuery(sql).addEntity(Loclist.class).list();
				for (int i = 0; i < list.size(); i++) {
					Loclist loc = new Loclist();//把"裸"数据组装到自己的employee类
					LoclistId locid = new LoclistId();
					Object[] object = (Object[]) list.get(i);
					locid.setF3(object[1].toString());
					loc.setId(locid);
					loclist.add(loc);
				} 
				return loclist;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Loclist> findAllProvince(final double countryId) {
		List<Loclist> list = hibernateTemplate.execute(new HibernateCallback<List<Loclist>>() {
			public List<Loclist> doInHibernate(Session session)
					throws HibernateException {
				String sql = " select * from loclist where location=? group by f4 ";
				SQLQuery query = session.createSQLQuery(sql);
				query.setDouble(0, countryId);
				List list = query.list();
				List<Loclist> loclist = new ArrayList<Loclist>();
				for (int i = 0; i < list.size(); i++) {
					Loclist loc = new Loclist();//把"裸"数据组装到自己的employee类
					LoclistId locid = new LoclistId();
					Object[] object = (Object[]) list.get(i);
					locid.setF6(object[3].toString());
					loc.setId(locid);
					loclist.add(loc);
				} 
				return loclist;
				//return query.addEntity(Loclist.class).list();
			}
		});
		return list;
	}
	
	public List<Loclist> findAllProvinceByContry(final String countryName) {
		final String sql = " select * from loclist where f3=? group by f4 ";
		List<Loclist> list = hibernateTemplate.execute(new HibernateCallback<List<Loclist>>() {
			public List<Loclist> doInHibernate(Session session)
					throws HibernateException {
			SQLQuery query = session.createSQLQuery(sql);
			query.setString(0, countryName);
			List list = query.list();
			List<Loclist> loclist = new ArrayList<Loclist>();
			for (int i = 0; i < list.size(); i++) {
				Loclist loc = new Loclist();//把"裸"数据组装到自己的employee类
				LoclistId locid = new LoclistId();
				Object[] object = (Object[]) list.get(i);
				if(object[3] != null){
					locid.setF6(object[3].toString());				
				}
				loc.setId(locid);
				loclist.add(loc);
			} 
			return loclist;
			//return query.addEntity(Loclist.class).list();
			}
		});
		return list;
	}
}
